﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace NPOITest
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
            //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
            using (var stream = File.Open(@"E:\HotelEntry.xls", FileMode.Open))
            {
                HSSFWorkbook workbook = new HSSFWorkbook(stream);
                //获取excel的sheet by name
                ISheet sheet = workbook.GetSheet("新酒店员工登记");
                DataTable table = new DataTable();
                //获取sheet的首行
                IRow headerRow = sheet.GetRow(0);
                //一行最后一个方格的编号 即总的列数
                int cellCount = headerRow.LastCellNum;
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                //最后一列的标号  即总的行数
                int rowCount = sheet.LastRowNum;

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    for (int j = 0; j < cellCount; j++)
                    {
                        var item = row.GetCell(j);
                        if (item != null)
                            dataRow[j] = GetCellValue(item, workbook);
                        else
                        {
                            Console.WriteLine(item);
                        }
                    }

                    table.Rows.Add(dataRow);
                }

                foreach (DataRow row in table.Rows)
                {
                    Console.Write(row.ItemArray[0] + " " + row.ItemArray[1] + " " + row.ItemArray[1] + " " + row.ItemArray[3]);
                    Console.WriteLine();
                }
            }

            Console.ReadKey();
        }

        private static string GetCellValue(ICell cell, IWorkbook workbook, CellType cachedFormulaResultType =CellType.Unknown)
        {
            string strCell = string.Empty;
            var cellType = cachedFormulaResultType;
            if (CellType.Unknown == cachedFormulaResultType)
                cellType = cell.CellType;
            switch (cellType)
            {
                case CellType.FORMULA:
                    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
                    strCell = evaluator.Evaluate(cell).StringValue;
                    if (string.IsNullOrEmpty(strCell))
                    {
                        strCell = GetCellValue(cell, workbook, cell.CachedFormulaResultType);
                    }
                    break;
                case CellType.STRING:
                    strCell = cell.StringCellValue;
                    break;
                case CellType.NUMERIC:
                    //strCell = cell.NumericCellValue.ToString();
                    //double DateTime类型都映射为NUMERIC
                    strCell = cell.ToString();
                    break;
                case CellType.BOOLEAN:
                    strCell = cell.BooleanCellValue.ToString();
                    break;
                case CellType.Unknown:
                case CellType.ERROR:
                case CellType.BLANK:
                    break;
            }
            return strCell;
        }
    }
}
